const sqlite3 = require('sqlite3').verbose();
const path = require('path');

class Database {
  constructor(dataDir = null) {
    // 如果没有指定数据目录，使用模块目录下的data文件夹
    const defaultDataDir = path.join(__dirname, '..', 'data');
    this.dataDir = dataDir || defaultDataDir;
    this.dbPath = path.join(this.dataDir, 'xiaohongshu_publisher.db');
    this.db = null;
    
    // 确保数据目录存在
    const fs = require('fs');
    if (!fs.existsSync(this.dataDir)) {
      fs.mkdirSync(this.dataDir, { recursive: true });
    }
  }

  init() {
    return new Promise((resolve, reject) => {
      this.db = new sqlite3.Database(this.dbPath, (err) => {
        if (err) {
          console.error('❌ 数据库连接失败:', err);
          reject(err);
          return;
        }
        console.log('✅ 数据库连接成功');
        this.createTables().then(() => {
          return this.migrateDatabase();
        }).then(resolve).catch(reject);
      })
    })
  }

  createTables() {
    return new Promise((resolve, reject) => {
      // 小红书账号表结构
      const createAccountsTable = `
        CREATE TABLE IF NOT EXISTS accounts (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          account_id TEXT UNIQUE NOT NULL,
          name TEXT NOT NULL,
          platform TEXT DEFAULT 'xiaohongshu',
          cookies TEXT,
          cookie_valid INTEGER DEFAULT NULL,
          last_validated_at DATETIME DEFAULT NULL,
          created_at DATETIME DEFAULT (datetime('now', 'localtime')),
          updated_at DATETIME DEFAULT (datetime('now', 'localtime'))
        )
      `;

      // 小红书发布记录表结构
      const createPublishRecordsTable = `
        CREATE TABLE IF NOT EXISTS publish_records (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          account_id TEXT NOT NULL,
          title TEXT NOT NULL,
          content TEXT,
          content_type TEXT NOT NULL,
          file_path TEXT,
          file_url TEXT,
          tags TEXT,
          status TEXT DEFAULT 'draft' CHECK (status IN ('draft', 'processing', 'published', 'failed')),
          publish_time TEXT,
          error_message TEXT,
          error_code TEXT,
          created_at DATETIME DEFAULT (datetime('now', 'localtime')),
          FOREIGN KEY (account_id) REFERENCES accounts (account_id)
        )
      `;

      this.db.run(createAccountsTable, (err) => {
        if (err) {
          reject(err);
          return;
        }
        
        this.db.run(createPublishRecordsTable, (err) => {
          if (err) {
            reject(err);
            return;
          }
          
          resolve();
        });
      });
    });
  }

  // 数据库迁移逻辑
  migrateDatabase() {
    return new Promise((resolve, reject) => {
      console.log('🔄 检查数据库版本和迁移...');

      // 检查 accounts 表是否有新字段
      const checkAccountFields = `
        PRAGMA table_info(accounts)
      `;

      this.db.all(checkAccountFields, [], (err, columns) => {
        if (err) {
          console.error('❌ 检查 accounts 表结构失败:', err);
          reject(err);
          return;
        }

        const columnNames = columns.map(col => col.name);
        const hasAccountId = columnNames.includes('account_id');
        const hasCookieValid = columnNames.includes('cookie_valid');
        const hasLastValidatedAt = columnNames.includes('last_validated_at');

        if (!hasAccountId || !hasCookieValid || !hasLastValidatedAt) {
          console.log('📊 需要迁移 accounts 表...');
          this.migrateAccountsTable().then(() => {
            this.checkPublishRecordsTable().then(resolve).catch(reject);
          }).catch(reject);
        } else {
          console.log('✅ accounts 表结构已是最新版本');
          this.checkPublishRecordsTable().then(resolve).catch(reject);
        }
      });
    });
  }

  // 迁移 accounts 表
  migrateAccountsTable() {
    return new Promise((resolve, reject) => {
      console.log('🔄 迁移 accounts 表...');

      const addAccountIdColumn = `ALTER TABLE accounts ADD COLUMN account_id TEXT`;
      const addCookieValidColumn = `ALTER TABLE accounts ADD COLUMN cookie_valid INTEGER DEFAULT NULL`;
      const addLastValidatedAtColumn = `ALTER TABLE accounts ADD COLUMN last_validated_at DATETIME DEFAULT NULL`;

      // 逐个添加缺失的列
      this.db.run(addAccountIdColumn, (err) => {
        if (err && !err.message.includes('duplicate column name')) {
          console.error('添加 account_id 列失败:', err);
          reject(err);
          return;
        }

        this.db.run(addCookieValidColumn, (err) => {
          if (err && !err.message.includes('duplicate column name')) {
            console.error('添加 cookie_valid 列失败:', err);
            reject(err);
            return;
          }

          this.db.run(addLastValidatedAtColumn, (err) => {
            if (err && !err.message.includes('duplicate column name')) {
              console.error('添加 last_validated_at 列失败:', err);
              reject(err);
              return;
            }

            // 为现有记录生成 account_id
            const updateAccountIds = `
              UPDATE accounts 
              SET account_id = 'account_' || id 
              WHERE account_id IS NULL
            `;

            this.db.run(updateAccountIds, (updateErr) => {
              if (updateErr) {
                console.error('更新 account_id 失败:', updateErr);
                reject(updateErr);
                return;
              }

              console.log('✅ accounts 表迁移完成');
              resolve();
            });
          });
        });
      });
    });
  }

  // 检查发布记录表
  checkPublishRecordsTable() {
    return new Promise((resolve, reject) => {
      const checkPublishRecordsFields = `PRAGMA table_info(publish_records)`;

      this.db.all(checkPublishRecordsFields, [], (err, columns) => {
        if (err) {
          console.error('❌ 检查 publish_records 表结构失败:', err);
          reject(err);
          return;
        }

        const accountIdColumn = columns.find(col => col.name === 'account_id');
        if (accountIdColumn && accountIdColumn.type === 'INTEGER') {
          console.log('🔄 需要迁移 publish_records 表的 account_id 类型...');
          // 这里可以添加类型迁移逻辑，但通常保持兼容性
        }

        console.log('✅ 数据库迁移检查完成');
        resolve();
      });
    });
  }

  // 确保验证字段存在的动态方法
  ensureValidationFields() {
    return new Promise((resolve, reject) => {
      const addCookieValidColumn = `ALTER TABLE accounts ADD COLUMN cookie_valid INTEGER DEFAULT NULL`;
      const addLastValidatedAtColumn = `ALTER TABLE accounts ADD COLUMN last_validated_at DATETIME DEFAULT NULL`;

      this.db.run(addCookieValidColumn, (err) => {
        if (err && !err.message.includes('duplicate column name')) {
          console.error('添加 cookie_valid 列失败:', err);
          reject(err);
          return;
        }

        this.db.run(addLastValidatedAtColumn, (err) => {
          if (err && !err.message.includes('duplicate column name')) {
            console.error('添加 last_validated_at 列失败:', err);
            reject(err);
            return;
          }

          resolve();
        });
      });
    });
  }

  saveAccount(accountData) {
    return new Promise((resolve, reject) => {
      const { account_id, name, platform, cookies } = accountData;
      
      // 检查必要字段
      if (!account_id) {
        reject(new Error('account_id 字段是必需的'));
        return;
      }
      
      // 检查账号是否已存在
      const checkSql = 'SELECT id FROM accounts WHERE account_id = ?';
      this.db.get(checkSql, [account_id], (checkErr, existingRow) => {
        if (checkErr) {
          reject(checkErr);
          return;
        }
        
        const cookiesStr = cookies ? JSON.stringify(cookies) : null;
        
        if (existingRow) {
          // 账号存在，执行更新
          const updateSql = `
            UPDATE accounts 
            SET name = ?, platform = ?, cookies = ?, updated_at = CURRENT_TIMESTAMP 
            WHERE account_id = ?
          `;
          
          this.db.run(updateSql, [name, platform || 'xiaohongshu', cookiesStr, account_id], function(err) {
            if (err) {
              reject(err);
              return;
            }
            
            resolve({ id: existingRow.id, account_id, name, platform: platform || 'xiaohongshu', cookies: cookies || [] });
          });
        } else {
          // 账号不存在，创建新账号
          const insertSql = `
            INSERT INTO accounts (account_id, name, platform, cookies, updated_at) 
            VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP)
          `;
          
          this.db.run(insertSql, [account_id, name, platform || 'xiaohongshu', cookiesStr], function(err) {
            if (err) {
              reject(err);
              return;
            }
            
            resolve({ id: this.lastID, account_id, name, platform: platform || 'xiaohongshu', cookies: cookies || [] });
          });
        }
      });
    });
  }

  getAccounts() {
    return new Promise((resolve, reject) => {
      const sql = 'SELECT * FROM accounts ORDER BY created_at DESC';
      this.db.all(sql, [], (err, rows) => {
        if (err) {
          reject(err);
          return;
        }
        
        // 解析 cookies
        const accounts = rows.map(row => ({
          ...row,
          cookies: row.cookies ? JSON.parse(row.cookies) : []
        }));
        
        resolve(accounts);
      });
    });
  }

  getAccountByAccountId(accountId) {
    return new Promise((resolve, reject) => {
      const sql = 'SELECT * FROM accounts WHERE account_id = ?';
      
      this.db.get(sql, [accountId], (err, row) => {
        if (err) {
          reject(err);
          return;
        }
        
        if (!row) {
          resolve(null);
          return;
        }
        
        const account = {
          ...row,
          cookies: row.cookies ? JSON.parse(row.cookies) : []
        };
        resolve(account);
      });
    });
  }

  deleteAccountByAccountId(accountId) {
    return new Promise((resolve, reject) => {
      const sql = 'DELETE FROM accounts WHERE account_id = ?';
      this.db.run(sql, [accountId], (err) => {
        if (err) {
          reject(err);
          return;
        }
        resolve();
      });
    });
  }

  // 更新账号验证状态
  updateAccountValidationStatus(accountId, isValid) {
    return new Promise(async (resolve, reject) => {
      try {
        // 确保验证字段存在
        await this.ensureValidationFields();
        
        const sql = `
          UPDATE accounts
          SET cookie_valid = ?, last_validated_at = CURRENT_TIMESTAMP
          WHERE account_id = ?
        `;
        
        this.db.run(sql, [isValid ? 1 : 0, accountId], function(err) {
          if (err) {
            console.error('更新数据库验证状态失败:', err);
            reject(err);
            return;
          }
          
          resolve({ account_id: accountId, cookie_valid: isValid, validated_at: new Date() });
        });
      } catch (error) {
        reject(error);
      }
    });
  }

  savePublishRecord(recordData) {
    return new Promise((resolve, reject) => {
      const { account_id, title, content, content_type, file_path, file_url, tags, status, publish_time, error_message, error_code } = recordData;
      
      const sql = `
        INSERT INTO publish_records (account_id, title, content, content_type, file_path, file_url, tags, status, publish_time, error_message, error_code) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      `;
      
      this.db.run(sql, [account_id, title, content, content_type, file_path, file_url, tags, status || 'draft', publish_time, error_message, error_code], function(err) {
        if (err) {
          reject(err);
          return;
        }
        resolve({ id: this.lastID, ...recordData });
      });
    });
  }

  updatePublishRecordStatus(recordId, statusData) {
    return new Promise((resolve, reject) => {
      const { status, publish_time, error_message, error_code } = statusData;
      const sql = `
        UPDATE publish_records 
        SET status = ?, publish_time = ?, error_message = ?, error_code = ?
        WHERE id = ?
      `;
      this.db.run(sql, [status, publish_time, error_message, error_code, recordId], function(err) {
        if (err) {
          reject(err);
          return;
        }
        resolve({ id: recordId, ...statusData });
      });
    });
  }

  getPublishRecords() {
    return new Promise((resolve, reject) => {
      const sql = `
        SELECT pr.*, a.name as account_name
        FROM publish_records pr 
        LEFT JOIN accounts a ON pr.account_id = a.account_id 
        ORDER BY pr.created_at DESC
      `;
      this.db.all(sql, [], (err, rows) => {
        if (err) {
          reject(err);
          return;
        }
        resolve(rows);
      });
    });
  }

  getPublishRecordsByAccountId(accountId) {
    return new Promise((resolve, reject) => {
      const sql = `
        SELECT pr.*, a.name as account_name
        FROM publish_records pr 
        LEFT JOIN accounts a ON pr.account_id = a.account_id 
        WHERE pr.account_id = ?
        ORDER BY pr.created_at DESC
      `;
      this.db.all(sql, [accountId], (err, rows) => {
        if (err) {
          reject(err);
          return;
        }
        resolve(rows);
      });
    });
  }

  getPublishRecordById(recordId) {
    return new Promise((resolve, reject) => {
      const sql = `
        SELECT pr.*, a.name as account_name
        FROM publish_records pr 
        LEFT JOIN accounts a ON pr.account_id = a.account_id 
        WHERE pr.id = ?
      `;
      this.db.get(sql, [recordId], (err, row) => {
        if (err) {
          reject(err);
          return;
        }
        resolve(row);
      });
    });
  }

  close() {
    if (this.db) {
      this.db.close();
    }
  }
}

module.exports = Database;
